create external table jms_dm.dm_mng_explosion_warehouse_network_punish_mid_dt
(
    final_sign_network_code string comment '派件网点编码',
    final_sign_network_name string comment '派件网点名称',
    end_agent_code          string comment '所属代理区编码',
    end_agent_name          string comment '所属代理区名称',
    end_provider_id         string comment '所属省份id',
    end_provider_name       string comment '所属省份名称',
    need_sign_count_1d      int comment '1天应签收',
    retention_count_1d      int comment '1天内滞留',
    need_sign_count_2d      int comment '2天应签收',
    retention_count_2d      int comment '2天内滞留',
    need_sign_count_3d      int comment '3天应签收',
    retention_count_3d      int comment '3天内滞留',
    need_sign_count_5d      int comment '5天应签收',
    retention_count_5d      int comment '5天内滞留',
    need_sign_count_7d      int comment '7天应签收',
    retention_count_7d      int comment '7天内滞留',
    scan_sum                int comment '时效签收量揽签分布汇总表',
    final_plan_sign_date    string comment '规划签收日期',
    need_sign_count         int comment '时效应签收量新末端签收汇总表(规划签收)',
    staff_code              string comment '员工编码',
    staff_name              string comment '员工名称',
    position_name           string comment '岗位名称.',
    virt_code               string comment '虚拟代理区code',
    virt_name               string comment '虚拟代理区name',
    3pressure               string comment '3日压力值',
    3frame                  string comment '3日票量',
    5pressure               string comment '5日压力值',
    5frame                  string comment '5日票量',
    7pressure               string comment '7日压力值',
    7frame                  string comment '7日票量',
    yali_flag               int comment '1代表压力值满足的要保留,0非代表不满足任何一种情况',
    zero_flag               int comment '1代表要剔除 应签收量>1日滞留量*50%  0要保留'
) comment '爆仓处罚报表中间表'
    partitioned by (dt string comment '规划签收日期')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_dm.db/external/dm_mng_explosion_warehouse_network_punish_mid_dt'
;

    alter table jms_dm.dm_mng_explosion_warehouse_network_punish_mid_dt
        add columns (
              end_city_id string comment '末端网点所属城市id'
            , end_city_name string comment '末端网点所属城市名称'
            , district_code string comment '转单责任网点片区code'
            , district_desc string comment '转单责任网点片区desc'
            , district_id string comment '转单责任网点片区id'
            ) cascade;

alter table jms_dm.dm_mng_explosion_warehouse_network_punish_mid_dt
    add columns (
        area_code string comment '区域code',
        area_name string comment '区域名字'
        ) cascade;
